package com.opentutor.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.opentutor.database.Database;
import com.opentutor.model.Video;

public class VideoDaoImpl implements VideoDao {

	@Override
	public List<Video> getAllVideos() {
		String query = "select * from video";
		List<Video> result = fetchVideosFromDatabase(query);
		return result;
	}

	@Override
	public List<Video> getAllVideosOfATopic(int topicId) {
		String query = "select * from video where topic_id = " + topicId;
		List<Video> result = fetchVideosFromDatabase(query);
		return result;
	}

	@Override
	public List<Video> getAllVideosOfAChapter(int chapterId) {
		String query = 	" select video.* from video "
					   +" inner join topic on topic.id = video.topic_id "  
					   +" where topic.chapter_id = " + chapterId;
		
		List<Video> result = fetchVideosFromDatabase(query);
		return result;
	}

	@Override
	public List<Video> getAllVideosOfACourse(int courseId) {
		String query = 	" select video.* from video "
				   +" inner join topic on topic.id = video.topic_id "  
				   +" inner join chapter on chapter.id = topic.chapter_id "  
				   +" where chapter.course_id = " + courseId;
	
		List<Video> result = fetchVideosFromDatabase(query);
		return result;
	}

	@Override
	public void addVideo(Video video) {
		try {

			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("insert into video(title, description, topic_id, video_id) values(?,?,?,?)");

			stmt.setString(1, video.getTitle());
			stmt.setString(2, video.getDescription());
			stmt.setInt(3, video.getTopicId());
			stmt.setString(4, video.getVideoId());
			
			stmt.executeUpdate();

		} catch (Exception e) {

			System.out.println(e.getMessage());
			e.printStackTrace();
		}

	}

	@Override
	public void updateVideo(Video video) {
		try {

			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("update video set title = ?, description = ?, topic_id = ?, video_id = ?, updated_date = now() where id = ?");

			stmt.setString(1, video.getTitle());
			stmt.setString(2, video.getDescription());
			stmt.setInt(3, video.getTopicId());
			stmt.setString(4, video.getVideoId());
			stmt.setInt(5, video.getId());
			
			stmt.executeUpdate();

		} catch (Exception e) {

			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}

	@Override
	public void deleteVideo(int videoId) {
		try {

			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("delete from video where id = ?");

			stmt.setInt(1, videoId);

			stmt.executeUpdate();

		} catch (Exception e) {

			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}
	
	private List<Video> fetchVideosFromDatabase(String query) {
		try {
			
			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn.prepareStatement(query);
			ResultSet result = stmt.executeQuery();

			List<Video> results = getAllVideosFromResultSet(result);

			return results;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	private List<Video> getAllVideosFromResultSet(ResultSet result)
			throws SQLException {
		List<Video> chapters = new ArrayList<Video>();
		while (result.next()) {
			Video chapter = getVideoFromResultSet(result);
			chapters.add(chapter);
		}
		return chapters;
	}

	private Video getVideoFromResultSet(ResultSet result) throws SQLException {
		Video topic = new Video();

		topic.setId(result.getInt("id"));
		topic.setTitle(result.getString("title"));
		topic.setDescription(result.getString("description"));
		topic.setVideoId(result.getString("video_id"));
		topic.setTopicId(result.getInt("topic_id"));

		return topic;
	}

}
